# -*- coding: utf-8 -*-

###########################################################################
#
#   Copyright (c) 2020  Bresee, Inc. All Rights Reserved.
#
# Licensed under the Apache License, Version 2.0 (the "License"
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
###########################################################################


import time
import utils.pgsql.base.pgconn as pgconn
import json
import copy

"""
activity table operate: 活动表
add 
delete
modify
...
"""
_tblName = "tbl_activity"

_DB_SCRIPT = """
CREATE TABLE IF NOT EXISTS {} (
                "activityId"  SERIAL PRIMARY KEY ,                       
                "activityName" VARCHAR(260) NOT NULL default '' ,         
                "operatorId"   INTEGER NOT NULL default 0,                
                "activityType" VARCHAR(32) NOT NULL default '', 
                "activityWay" VARCHAR(128) NOT NULL default '',         
                "facePeriod" INTEGER NOT NULL default 0 ,               
                "caseId" INTEGER NOT NULL default 0 ,
                "templateId"    INTEGER NOT NULL default 0,         
                "faceNum" INTEGER NOT NULL default 0,                    
                "deviceIds" VARCHAR(1024) NOT NULL default '',            
                "videoConfigList" json, 
                "sameDeviceRule" VARCHAR(32) NOT NULL default '' ,    
                "composeWay" VARCHAR(32) NOT NULL default '',    
                "createTime" VARCHAR(32) NOT NULL default '',
                "updateTime" VARCHAR(32) NOT NULL default '',
                "activitygroupId" INTEGER NOT NULL default 0,
                "activitygroupNum" INTEGER NOT NULL default 0
                );

""".format(
    _tblName
)


class PgActivity:
    def __init__(self):
        self._pgConn = pgconn.pgMainConn

    def __del__(self):
        pass

    def createTbl(self):
        """
        create
        :return:
        """
        return self._pgConn.exec(_DB_SCRIPT)

    def add(self, dic) -> dict:
        dic["createTime"] = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
        dic["updateTime"] = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
        if "videoConfigList" in dic:
            dic["videoConfigList"] = json.dumps(dic["videoConfigList"])

        sql_key = '\"' + '\", \"'.join(dic.keys()) + '\"'
        sql_val = ''
        for key in dic:
            if type(dic[key]) is str:
                sql_val = sql_val + '\'' + dic[key] + '\','
            else:
                sql_val = sql_val + str(dic[key]) + ' ,'

        sql = "insert into {} ({}) values ({}) ".format(_tblName, sql_key, sql_val[:-1])

        return self._pgConn.exec(sql)

    
    def modify(self, ids, dic) -> bool:
        # 修改，
        dic["updateTime"] = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
        if "videoConfigList" in dic:
            dic["videoConfigList"] = json.dumps(dic["videoConfigList"])

        sql_key = ''
        for key in dic:
            if type(dic[key]) is str:
                sql_key = sql_key + '\"' + key + '\"' + ' = \'' + dic[key] + '\','
            else:
                sql_key = sql_key + '\"' + key + '\"' + ' = ' + str(dic[key]) + ' ,'

        sql =  "update {} set {} where  ".format(_tblName, sql_key[:-1])+ f""" "activityId" = '{ids}' """
        return self._pgConn.exec(sql)

   
    def delete(self, ids) -> bool:
        sql = f"""delete from {_tblName} where "activityId" = '{ids}' """
        return self._pgConn.exec(sql)

    
    def activityNameget(self, activityName, caseId) -> list:
        sql = 'select "activityName" from {}  where  "activityName" = \'{}\' and "caseId" = \'{}\' '.format(
            _tblName, activityName, caseId
        )
        return self._pgConn.exec_fetchall(sql)

    def acitivName(self, templateId) -> list:
        sql = 'select "activityName" from {}  where  "templateId" = \'{}\'  '.format(
            _tblName, templateId
        )
        return self._pgConn.exec_fetchone(sql)

    def activityNameeditget(self, activityName, caseId, activityId) -> list:
        sql = 'select "activityName" from {}  where  "activityName" = \'{}\' and "caseId" = \'{}\'  and "activityId" != \'{}\' '.format(
            _tblName, activityName, caseId, activityId
        )
        return self._pgConn.exec_fetchall(sql)

    def activitycaseget(self, caseId) -> list:
        sql = "select * from {}  where   \"caseId\" = '{}' ".format(_tblName, caseId)
        return self._pgConn.exec_fetchall(sql)

    def get(self, ids) -> dict:
        sql = f"""select * from {_tblName} where "activityId" = '{ids}' order by \"activityId\" """
        return self._pgConn.exec_fetchone(sql)

    def get_caseId(self, caseId) -> dict:
        sql = 'select "activityName","deviceIds","videoConfigList" from {} where "caseId" = \'{}\'  order by "activityId" '.format(
            _tblName, caseId
        )
        return self._pgConn._exec_fetchall(sql)


    def get_activityName(self, templateId) -> dict:
        sql = 'select "activityName" from {} where "templateId" = \'{}\' '.format(
            _tblName, templateId
        )
        return self._pgConn._exec_fetchall(sql)

    def all(self) -> list:
        sql = 'select a.*,n."caseName" from {} a left Join tbl_case n on a."caseId"=n."caseId" where n."dataValid" !=0  order by a."activityId" '.format(
            _tblName
        )
        print(sql)
        return self._pgConn.exec_fetchall(sql)

    def allByOperatorId(self, operatorId) -> list:
        sql = f"""select a.*,n.\"caseName\" from {_tblName}  a left Join tbl_case n on a.\"caseId\"=n.\"caseId\" where n.\"dataValid\" !=0 and a.\"operatorId\"= {operatorId} order by a.\"activityId\" """
        return self._pgConn.exec_fetchall(sql)

    def allByCaseId(self, caseId, limit="") -> list:
        tmp = f' and a."activityName" SIMILAR TO \'%({limit})%\' ' if limit else ""
        sql = 'select a.*,n."caseName" from {} a left Join tbl_case n on {}=n."caseId"  where n."dataValid" !=0  and a."caseId" = \'{}\' {} order by "activityId" '.format(
            _tblName, caseId, caseId, tmp
        )

        # sql = f"""select * from {_tblName} where "caseId"= {caseId} """
        return self._pgConn.exec_fetchall(sql)

    def getTemplInfo(self, caseId) -> list:
        """Find the template information of the template group corresponding to all activities in the specified scenic spot"""
        sql = f"""SELECT a.* , t."templateData" ,t."templateCloudId",t."ticketPrice",t."templateName" from tbl_activity a  inner join tbl_template t on a."templateId" = t."templateId" and a."caseId" = {caseId} """
        lst = self._pgConn.exec_fetchall(sql)

        retLst = []
        for item in lst:
            deepItem = copy.deepcopy(item)
            deepItem["templateData"] = json.loads(deepItem["templateData"])
            retLst.append(deepItem)

        return retLst

    # 获取模板对应活动
    def templateName_select_videoConfigList(self, templateName):
        sql = 'select "videoConfigList" from {}  where "activityName" = \'{}\''.format(
            _tblName, templateName
        )
        print(sql)
        return self._pgConn.exec_fetchone(sql)


    def notsyn_Activity(self,case_id=None) -> list:
        extral = f' and t."caseId" = {case_id}' if case_id else ''
        sql = 'SELECT t.*,p.ai_ip FROM {} as t LEFT JOIN playground as p ' \
              'on t."caseId" =p."case_id" AND STRPOS(t."activityName",p."name") > 0 where "synState"=\'0\' {};'.format(
            _tblName, extral
        )
        return self._pgConn.exec_fetchall(sql)

    # 更新模板同步状态
    def update_Activity_synState_sql(self, ActivityId_str) -> bool:

        sql =  "update {} set \"synState\" =1 where \"activityId\" = '{}'  ".format(_tblName,ActivityId_str)
        print("sql",sql)
        return self._pgConn.exec(sql)